package gov.nhso.prov.acc.apps.acc7;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc7ExpenseBean;
import gov.nhso.prov.acc.beans.Acc7ExpenseDetailBean;
import gov.nhso.prov.acc.beans.Acc7TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes","deprecation"})
public class ACC70302 extends Utility{
	public String url = "/apps/acc7/ACC70302A.jsp";
	
	public ACC70302(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		} else if (job.equals("add")){
			this.add(request);
		} else if (job.equals("search")){
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		} else if (job.equals("save")){
			this.save(request);
		} else if (job.equals("viewEdit")){
			this.viewEdit(request);
		} else if (job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		} else if (job.equals("edit")){
			this.edit(request);
		} else if (job.equals("confirm")){
			this.confirm(request);
		} else if (job.equals("del")){
			this.del(request);
		} else if (job.equals("downloadTemplate")){
			this.downloadTemplate(request, response);
		}
	}
	private String init(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			PageBean pageBean = new PageBean();
			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			this.beanUtil().copyRequest2Bean(request, expenseBean);
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String refNo = this.getAttr(request, "refNo");
			String condition = "";
			
			if(!dao.chkData(startDate).equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}			
			if(!dao.chkData(endDate).equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			expenseBean.setRefNo(refNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setDocNo(docNo);
			expenseBean.setActive("Y");
			this.beanUtil().copyRequest2Bean(request, pageBean);
			if (this.user(request).getAreaLevel().equals("2")){	
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			pageBean = dao.selectPage(expenseBean,condition, pageBean);
			
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String add(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			List expenseCategoryList = new ArrayList();
			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			expenseCategoryBean.setCategoryAcc("7");
			expenseCategoryBean.setCategoryApply("Y");
			expenseCategoryList = dao.selectList(expenseCategoryBean);
			
			expenseBean.setDocNo("สร้างจากระบบ");
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("expenseCategoryList", expenseCategoryList);
			request.setAttribute("job", "save");
			request.setAttribute("display", "none");
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc7/ACC70302B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			Acc7ExpenseDetailBean expenseDetailBean = new Acc7ExpenseDetailBean();
			
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String refNo = this.getAttr(request, "refNo");
			String expenseDate = this.getAttr(request, "expenseDate");
			String[] expenseAmount = request.getParameterValues("expenseAmount");
			String[] categoryId = request.getParameterValues("categoryId");
			String[] expenseDetailDesc = request.getParameterValues("expenseDetailDesc");
			String docNo = this.genCode(dao).getExpenseDocNoAcc7(budgetYear, provinceId, "E");
			
			if(expenseAmount != null && categoryId != null){
				
				expenseBean.setDocNo(docNo);
				expenseBean.setProvinceId(provinceId);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setExpenseDesc(expenseDesc);
				expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
				expenseBean.setRefNo(refNo);
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				expenseBean.setCreatedBy(this.user(request).getUserId());
				dao.insert(expenseBean);
				
				for (int i=0 ;i < expenseAmount.length;i++){
					expenseDetailBean = new Acc7ExpenseDetailBean();
					expenseDetailBean.setDocNo(docNo);
					expenseDetailBean.setSeq(String.valueOf(i+1));
					expenseDetailBean.setCategoryId(categoryId[i]);
					expenseDetailBean.setExpenseAmount(expenseAmount[i].replace(",", ""));
					expenseDetailBean.setExpenseDetailDesc(this.encodeUTF(expenseDetailDesc[i]));
					expenseDetailBean.setUpdatedBy(this.user(request).getUserId());
					expenseDetailBean.setCreatedBy(this.user(request).getUserId());
					dao.insert(expenseDetailBean);
				}
				
			}
			
			request.setAttribute("docNo", expenseBean.getDocNo());
			this.message(request).setMsgCode("I002");
			conn.commit();
	
		} catch (Exception e) {
			
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String edit(HttpServletRequest request){
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			Acc7ExpenseDetailBean expenseDetailBean = new Acc7ExpenseDetailBean();
			
			String docNo = this.getAttr(request, "docNo");
			String refNo = this.getAttr(request, "refNo");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String expenseDate = this.getAttr(request, "expenseDate");
			String[] expenseAmount = request.getParameterValues("expenseAmount");
			String[] categoryId = request.getParameterValues("categoryId");
			String[] expenseDetailDesc = request.getParameterValues("expenseDetailDesc");
			
			if(expenseAmount != null && categoryId != null){
				
				expenseBean.setDocNo(docNo);
				expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
				expenseBean.setRefNo(refNo);
				expenseBean.setExpenseDesc(expenseDesc);
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				expenseBean.setUpdatedDate(this.sysDate);
				dao.update(expenseBean);
				
				expenseDetailBean.setDocNo(docNo);
				dao.delete(expenseDetailBean);
				
				for (int i=0 ;i < expenseAmount.length;i++){
					
					expenseDetailBean = new Acc7ExpenseDetailBean();
					expenseDetailBean.setDocNo(docNo);
					expenseDetailBean.setSeq(String.valueOf(i+1));
					expenseDetailBean.setCategoryId(categoryId[i]);
					expenseDetailBean.setExpenseAmount(expenseAmount[i].replace(",", ""));
					expenseDetailBean.setExpenseDetailDesc(this.encodeUTF(expenseDetailDesc[i]));
					expenseDetailBean.setUpdatedBy(this.user(request).getUserId());
					expenseDetailBean.setCreatedBy(this.user(request).getUserId());
					dao.insert(expenseDetailBean);
				
				}
				
			}
			
			request.setAttribute("docNo", expenseBean.getDocNo());
			this.message(request).setMsgCode("I002");
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	
	private String viewEdit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			
			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			List listExpenseDetailBean = new ArrayList();
			Acc7ExpenseDetailBean expenseDetailBean = new Acc7ExpenseDetailBean();
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List expenseCategoryList = new ArrayList();
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean = (Acc7ExpenseBean)dao.select(expenseBean);
			expenseDetailBean.setDocNo(docNo);
			listExpenseDetailBean = dao.selectList(expenseDetailBean);
			expenseCategoryBean.setCategoryAcc("7");
			expenseCategoryBean.setCategoryApply("Y");
			expenseCategoryList = dao.selectList(expenseCategoryBean);
			
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseDetailBean", listExpenseDetailBean);
			request.setAttribute("expenseCategoryList", expenseCategoryList);
			request.setAttribute("job", "edit");
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc7/ACC70302B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String confirm(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String refNo = this.getAttr(request, "refNo");
			String expenseDesc = this.getAttr(request, "expenseDesc");
			String expenseDate = this.getAttr(request, "expenseDate");
			String[] expenseAmount = request.getParameterValues("expenseAmount");
			String[] categoryId = request.getParameterValues("categoryId");
			String[] expenseDetailDesc = request.getParameterValues("expenseDetailDesc");
			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			Acc7ExpenseBean bean = new Acc7ExpenseBean();
			Acc7ExpenseDetailBean expenseDetailBean = new Acc7ExpenseDetailBean();
			Acc7TransactionBean transactionBean = new Acc7TransactionBean();
			String ts = "";

			if(!docNo.equals("สร้างจากระบบ")){
				bean.setDocNo(docNo);
				bean.setProvinceId(provinceId);
				bean.setBudgetYear(budgetYear);
				dao.select(bean);
				if (bean.getExpenseStatus().equals("P")) {
					expenseBean.setDocNo(docNo);
					expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
					expenseBean.setRefNo(refNo);
					expenseBean.setExpenseDesc(expenseDesc);
					expenseBean.setExpenseStatus("Y");
					expenseBean.setUpdatedBy(this.user(request).getUserId());
					expenseBean.setUpdatedDate(this.sysDate);
					dao.update(expenseBean);
					expenseDetailBean.setDocNo(docNo);
					dao.delete(expenseDetailBean);
					for (int i=0 ;i < expenseAmount.length;i++){
						ts = this.dateUtil().getCurrentTimeStamp2DB();
						expenseDetailBean = new Acc7ExpenseDetailBean();
						expenseDetailBean.setDocNo(docNo);
						expenseDetailBean.setSeq(String.valueOf(i+1));
						expenseDetailBean.setCategoryId(categoryId[i]);
						expenseDetailBean.setExpenseAmount(expenseAmount[i].replace(",", ""));
						expenseDetailBean.setExpenseDetailDesc(this.encodeUTF(expenseDetailDesc[i]));
						expenseDetailBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
						expenseDetailBean.setUpdatedBy(this.user(request).getUserId());
						expenseDetailBean.setCreatedBy(this.user(request).getUserId());
						dao.insert(expenseDetailBean);

						transactionBean = new Acc7TransactionBean();
						transactionBean.setBudgetYear(budgetYear);
						transactionBean.setProvinceId(provinceId);
						transactionBean.setExpenseAmount(expenseAmount[i].replace(",", ""));
						transactionBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
						transactionBean.setReasonCode("00");
						transactionBean.setTransactionType("E");
						transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
						transactionBean.setRefNo(refNo);
						transactionBean.setCategoryId(categoryId[i]);
						dao.insert(transactionBean);
					}
					this.message(request).setMsgCode("I002");
				} else {
					this.message(request).setMsg("E", "ไม่สามารถยืนยันได้ สถานะไม่ได้อยู่เตรียมดำเนินการ กรุณาตรวจสอบ");
				}
				
			}else{
				docNo = this.genCode(dao).getExpenseDocNoAcc7(budgetYear, provinceId, "E");
				if(expenseAmount != null && categoryId != null){
					expenseBean.setDocNo(docNo);
					expenseBean.setProvinceId(provinceId);
					expenseBean.setBudgetYear(budgetYear);
					expenseBean.setExpenseDesc(expenseDesc);
					expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
					expenseBean.setRefNo(refNo);
					expenseBean.setExpenseStatus("Y");
					expenseBean.setUpdatedBy(this.user(request).getUserId());
					expenseBean.setCreatedBy(this.user(request).getUserId());
					dao.insert(expenseBean);
					for (int i=0 ;i < expenseAmount.length;i++){
						ts = this.dateUtil().getCurrentTimeStamp2DB();
						expenseDetailBean = new Acc7ExpenseDetailBean();
						expenseDetailBean.setDocNo(docNo);
						expenseDetailBean.setSeq(String.valueOf(i+1));
						expenseDetailBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
						expenseDetailBean.setCategoryId(categoryId[i]);
						expenseDetailBean.setExpenseAmount(expenseAmount[i].replace(",", ""));
						expenseDetailBean.setExpenseDetailDesc(this.encodeUTF(expenseDetailDesc[i]));
						expenseDetailBean.setUpdatedBy(this.user(request).getUserId());
						expenseDetailBean.setCreatedBy(this.user(request).getUserId());
						dao.insert(expenseDetailBean);

						transactionBean = new Acc7TransactionBean();
						transactionBean.setBudgetYear(budgetYear);
						transactionBean.setProvinceId(provinceId);
						transactionBean.setExpenseAmount(expenseAmount[i].replace(",", ""));
						transactionBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
						transactionBean.setReasonCode("00");
						transactionBean.setTransactionType("E");
						transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
						transactionBean.setRefNo(refNo);
						transactionBean.setCategoryId(categoryId[i]);
						dao.insert(transactionBean);
					}
				}
				
				docNo = expenseBean.getDocNo();
				budgetYear = expenseBean.getBudgetYear();
				provinceId = expenseBean.getProvinceId();
				this.message(request).setMsgCode("I002");
			}
			
			request.setAttribute("docNo", docNo);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.viewEdit(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String del(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc7ExpenseBean expenseBean = new Acc7ExpenseBean();
			Acc7ExpenseDetailBean expenseDetailBean = new Acc7ExpenseDetailBean();
			
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setActive("N");
			expenseBean.setUpdatedBy(this.user(request).getUserId());
			expenseBean.setUpdatedDate(this.sysDate);
			dao.update(expenseBean);
			expenseDetailBean.setDocNo(docNo);
			expenseDetailBean.setActive("N");
			expenseDetailBean.setUpdatedBy(this.user(request).getUserId());
			expenseDetailBean.setUpdatedDate(this.sysDate);
			dao.update(expenseDetailBean);
			
			request.setAttribute("docNo", "");
			this.message(request).setMsgCode("I002");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private void downloadTemplate(HttpServletRequest request, HttpServletResponse response){
		System.out.println("downloadTemplate");
		try {
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\"Template.xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sapData");
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cellAmount = row.createCell(0);
			HSSFCell cellCategory = row.createCell(1);
			HSSFCell cellDesc = row.createCell(2);
			
			sheet.setDefaultColumnWidth((short) 35);
			cellAmount.setCellStyle(cellStyle);
			cellCategory.setCellStyle(cellStyle);
			cellDesc.setCellStyle(cellStyle);
			
			cellAmount.setCellValue("จำนวนเงิน");
			cellCategory.setCellValue("รหัสหมวดการจ่าย");
			cellDesc.setCellValue("คำอธิบาย");
			
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			this.url = "";
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
